package com.example.teacherservice.repository;

import com.example.teacherservice.dto.StudentPracticeDTO;
import com.example.teacherservice.entity.StuPractice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Set;

@Repository
public interface StuPracticeRepository extends JpaRepository<StuPractice, Long> {

    @Query("SELECT sp FROM StuPractice sp JOIN FETCH sp.student WHERE sp.practice.id = :practiceId")
    List<StuPractice> findByPracticeIdWithStudents(@Param("practiceId") Long practiceId);

    @Query("SELECT sp FROM StuPractice sp WHERE sp.practice.id = :practiceId AND sp.student.id IN :studentIds")
    List<StuPractice> findByPracticeIdAndStudentIdIn(
            @Param("practiceId") Long practiceId,
            @Param("studentIds") List<Long> studentIds
    );

    @Query("SELECT sp.student.id FROM StuPractice sp WHERE sp.practice.id = :practiceId")
    Set<Long> findStudentIdsByPracticeId(@Param("practiceId") Long practiceId);

    // 检查学生是否已加入某个实践
    @Query("SELECT COUNT(sp) > 0 FROM StuPractice sp " +
            "WHERE sp.student.id = :studentId AND sp.practice.id = :practiceId ")
    boolean existsByStudentAndPractice(@Param("studentId") Long studentId,
                                       @Param("practiceId") Long practiceId);

    // 获取学生加入的所有实践
    @Query("SELECT new com.example.teacherservice.dto.StudentPracticeDTO(" +
            "sp.id, p.id, p.praName, t.teaName, c.couName, p.location, " +
            "p.startTime, p.endTime, p.status, CURRENT_TIMESTAMP) " + // 使用sp.createTime替代CURRENT_TIMESTAMP
            "FROM StuPractice sp " +
            "JOIN sp.practice p " +
            "LEFT JOIN Teacher t ON p.teaId = t.id " +
            "LEFT JOIN Course c ON p.couId = c.id " +
            "WHERE sp.student.id = :studentId AND p.deleted = 0")
    List<StudentPracticeDTO> findPracticesByStudentId(@Param("studentId") Long studentId);

    // 学生加入实践 - 需要改为使用EntityManager或自定义实现
    // 由于使用实体关联，直接插入需要特殊处理
    @Modifying
    @Query(value = "INSERT INTO stu_practice(stu_id, pra_id) VALUES (:studentId, :practiceId)", nativeQuery = true)
    @Transactional
    int joinPractice(@Param("studentId") Long studentId,
                     @Param("practiceId") Long practiceId);

    // 学生退出实践
    // 学生退出实践 - 直接删除记录
    @Modifying
    @Query("DELETE FROM StuPractice sp " +
            "WHERE sp.student.id = :studentId AND sp.practice.id = :practiceId")
    @Transactional
    void deleteStudentPractice(@Param("studentId") Long studentId,
                               @Param("practiceId") Long practiceId);

    /**
     * 学生退出实践活动
     * @param studentId 学生ID
     * @param practiceId 实践活动ID
     * @return 影响的行数
     */
    @Modifying
    @Transactional
    @Query("DELETE FROM StuPractice sp " +
            "WHERE sp.student.id = :studentId AND sp.practice.id = :practiceId")
    int quitPractice(@Param("studentId") Long studentId,
                     @Param("practiceId") Long practiceId);

    // 可选：先检查是否存在关联记录
    boolean existsByStudentIdAndPracticeId(Long studentId, Long practiceId);
}